/*=====================================================================

  File:      InstallCS.sql
  Summary:   SQL script to create the schema for the DB3D sample
  Date:	     May 27, 2006

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */


IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'db3d')
    DROP DATABASE [db3d];

-- If the database has any other open connections close the network connection.
IF @@ERROR = 3702 
    RAISERROR('[db3d] database cannot be dropped because there are still other open connections', 127, 127) WITH NOWAIT, LOG;
GO

CREATE DATABASE db3d
GO

USE db3d
GO

-- create CLR objects (assemblies, aggregates and UDTs)

DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = substring(physical_name, 1, 
  patindex('%Microsoft SQL Server%', physical_name) - 1) 
  + 'Microsoft SQL Server\100\Samples\Integrated Samples\' 
FROM master.sys.database_files 
WHERE name = 'master';
    
CREATE ASSEMBLY db3dsql FROM @SamplesPath + 'DB3D\CS\db3dsql\bin\debug\db3dsql.dll'
GO

CREATE TYPE Point3D EXTERNAL NAME db3dsql.[Microsoft.Samples.SqlServer.DB3D.Point3D]
GO

CREATE TYPE Triangle3D EXTERNAL NAME db3dsql.[Microsoft.Samples.SqlServer.DB3D.Triangle]
GO

CREATE AGGREGATE Surface (@input Triangle3D) RETURNS FLOAT
EXTERNAL NAME db3dsql.[Microsoft.Samples.SqlServer.DB3D.Surface]
GO

-- create tables

CREATE TABLE Wireframe (
	WireframeID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	WireframeName VARCHAR(255) NOT NULL UNIQUE
)

CREATE TABLE Triangle (
	TriangleID INT NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY(1,1),
	WireframeID INT NOT NULL REFERENCES Wireframe (WireframeID),
	TriangleObj Triangle3D NOT NULL
)

CREATE CLUSTERED INDEX IX_Triangle_WireframeID ON Triangle (WireframeID)

GO

-- create stored-procedures

CREATE PROCEDURE GetWireframe (@WireframeID INT, @WireframeName VARCHAR(255) OUTPUT) AS
	SELECT @WireframeName = WireframeName FROM Wireframe WHERE WireframeID = @WireframeID
    SELECT TriangleID, TriangleObj FROM Triangle WHERE WireframeID = @WireframeID
GO

CREATE PROCEDURE CreateWireframe (@WireframeName VARCHAR(255)) AS
	INSERT INTO Wireframe (WireframeName) VALUES (@WireframeName)
	SELECT SCOPE_IDENTITY()
GO
	
CREATE PROCEDURE ListWireframes AS
	SELECT WireframeID, WireframeName FROM Wireframe ORDER BY WireframeName
GO

CREATE PROCEDURE ListWireframesDetailed AS
	SELECT w.WireframeID, WireframeName, COUNT(*) AS TriangleCount, dbo.Surface(t.TriangleObj) AS Surface
	FROM Wireframe w
	INNER JOIN Triangle t ON w.WireframeID = t.WireframeID
	GROUP BY w.WireframeID, WireframeName
GO

CREATE PROCEDURE CreateTriangle (@WireframeID INT, @Triangle Triangle3D) AS
    INSERT INTO Triangle (WireframeID, TriangleObj) VALUES (@WireframeID, @Triangle)
    SELECT SCOPE_IDENTITY()
GO

CREATE PROCEDURE DeleteWireframe (@WireframeID INT) AS
	DELETE FROM Triangle WHERE WireframeID = @WireframeID
	DELETE FROM Wireframe WHERE WireframeID = @WireframeID
GO
